﻿Imports Entidad
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Public Class KardexDatos

    Private Path As String = "H:\DATOS\CFG\"

    Function obtenerPeriodo(codCIA As String) As String
        Dim valor As String = ""
        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + "MATROCFG.DBF") & ";"
        Dim query As String = "SELECT anhos + meses AS Periodo FROM MATROCFG WHERE codcia = '" & codCIA & "'"
        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim comando As New OdbcCommand
                With comando
                    .Connection = dbConn
                    .CommandText = query
                End With
                Dim ds As New DataSet
                Dim adapter As New OdbcDataAdapter(comando)
                adapter.Fill(ds)

                If ds.Tables(0).Rows.Count > 0 Then
                    valor = ds.Tables(0).Rows(0)("Periodo").ToString.Trim.Substring(2)
                End If
            Catch ex As Exception
                valor = ""
            Finally
                dbConn.Close()
            End Try
        End Using
        Return valor
    End Function

    Function listadoStockArticulos(empresa As String, codArticulo As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim periodo As String = obtenerPeriodo(empresa)

        Dim query As String = "SELECT ART_Codigo, Articulo, UNM_Abreviatura, PROV_Codigo, Proveedor, SICOPE, SIS, Diferencia " &
                              "FROM VW_DiferenciaStock " &
                              IIf(codArticulo <> "", "WHERE ART_Codigo = '" & codArticulo & "' ", "WHERE SICOPE - SIS > 0 ") &
                              "ORDER BY ART_Codigo"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarMovimientosXArticulo(codArticulo As String, fechaInicial As String, fechaFinal As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT u.UBI_Descripcion, CAST(dbo.FNX_DevuelveStock(au.ART_Codigo,ku.UBI_Codigo,@INI) AS DECIMAL(10,2)) AS InvInicial, " &
                              "CAST(SUM((CASE WHEN td.TDO_Factor = 1 THEN ku.KUC_Cantidad ELSE 0 END)) AS DECIMAL(10,2)) AS 'Ingreso', " &
                              "CAST(SUM((CASE WHEN td.TDO_Factor = -1 THEN ku.KUC_Cantidad ELSE 0 END)) AS DECIMAL(10,2)) AS 'Salida', " &
                              "CAST(dbo.FNX_DevuelveStock(au.ART_Codigo,ku.UBI_Codigo,@INI) + " &
                              "SUM((CASE WHEN td.TDO_Factor = 1 THEN ku.KUC_Cantidad ELSE 0 END)) - " &
                              "SUM((CASE WHEN td.TDO_Factor = -1 THEN ku.KUC_Cantidad ELSE 0 END)) AS DECIMAL(10,2)) AS Stock, ku.UBI_Codigo " &
                              "FROM Kardex_Ubicacion ku " &
                              "INNER JOIN ArticuloUbicacion au " &
                              "ON au.CIA = ku.CIA AND au.SUC_Codigo = ku.SUC_Codigo " &
                              "AND au.UBI_Codigo = ku.UBI_Codigo AND au.ART_Codigo = ku.ART_Codigo " &
                              "AND au.UAR_Activo = '1' AND au.ALM_Codigo = '00' " &
                              "INNER JOIN Ubicacion u " &
                              "ON u.UBI_Codigo = au.UBI_Codigo AND u.UBI_Activo = '1' " &
                              "INNER JOIN TipoDocumento td " &
                              "ON td.TDO_Codigo = ku.TDOC_Codigo AND td.TDO_Activo = '1' " &
                              "WHERE ku.CIA = '01' AND ku.SUC_Codigo = '01' " &
                              "AND ku.ART_Codigo = @ART " &
                              "AND ku.KUC_Activo = '1' " &
                              "AND CAST(CONVERT(VARCHAR(15),ku.KUC_Fecha,103) AS DATETIME) BETWEEN @INI AND @FIN " &
                              "GROUP BY u.UBI_Descripcion, au.ART_Codigo, ku.UBI_Codigo"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@ART", codArticulo)
            .Parameters.AddWithValue("@INI", fechaInicial)
            .Parameters.AddWithValue("@FIN", fechaFinal)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarMovimientos(codArticulo As String, codUbicacion As String, fechaInicial As String, fechaFinal As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT '' AS Tipo, 'STOCK INICIAL' AS Documento, " &
                              "@INI AS Fecha, " &
                              "CAST(dbo.FNX_DevuelveStock(@ART,@UBI,@INI) AS DECIMAL(10,2)) AS Cantidad, '' AS Usuario " &
                              "UNION " &
                              "SELECT (CASE WHEN dpad.DCP_Codigo IS NOT NULL THEN td1.TDO_Abreviatura " &
                              "WHEN dcad.DCI_Codigo IS NOT NULL THEN td2.TDO_Abreviatura " &
                              "ELSE td3.TDO_Abreviatura END) AS Tipo, " &
                              "(CASE WHEN dpad.DCP_Codigo IS NOT NULL THEN dpad.DCP_Codigo " &
                              "WHEN dcad.DCI_Codigo IS NOT NULL THEN dcad.DCI_Codigo " &
                              "ELSE dad.DCA_Codigo END) AS Documento, " &
                              "CONVERT(VARCHAR(15),ku.KUC_Fecha,103) AS Fecha, " &
                              "CAST(ku.KUC_Cantidad AS DECIMAL(10,2)) AS Cantidad, pp.PER_Nombres + ' ' + pp.PER_Apellidos AS Usuario " &
                              "FROM Kardex_Ubicacion ku " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                              "ON dadpu.CIA = ku.CIA AND dadpu.SUC_Codigo = ku.SUC_Codigo AND dadpu.TDOC_Codigo = ku.TDOC_Codigo " &
                              "AND dadpu.DCA_Codigo = ku.KUC_Documento AND dadpu.DADPU_Codigo = ku.KUC_Detalle " &
                              "INNER JOIN TipoDocumento td3 " &
                              "ON td3.TDO_Codigo = dadpu.TDOC_Codigo AND td3.TDO_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                              "ON dadp.CIA = dadpu.CIA AND dadp.SUC_Codigo = dadpu.SUC_Codigo AND dadp.TDOC_Codigo = dadpu.TDOC_Codigo " &
                              "AND dadp.DCA_Codigo = dadpu.DCA_Codigo AND dadp.DADP_Codigo = dadpu.DADP_Codigo " &
                              "INNER JOIN PersonaPerfil pp " &
                              "ON pp.CIA = dadp.CIA AND pp.SUC_Codigo = dadp.SUC_Codigo AND pp.ALM_Codigo = dadp.ALM_Codigo " &
                              "AND pp.PER_Codigo = dadp.PICK_Codigo " &
                              "INNER JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.CIA = dadp.CIA AND dad.SUC = dadp.SUC_Codigo AND dad.TDOC_Codigo = dadp.TDOC_Codigo " &
                              "AND dad.DCA_Codigo = dadp.DCA_Codigo AND dad.DAD_Codigo = dadp.DAD_Codigo " &
                              "LEFT JOIN Documento_Proveedor_Almacen_Detalle dpad " &
                              "ON dpad.CIA_Almacen = dad.CIA AND dpad.SUC_Almacen = dad.SUC AND dpad.TDOC_Almacen = dad.TDOC_Codigo " &
                              "AND dpad.DCA_Codigo = dad.DCA_Codigo AND dpad.DAD_Codigo = dad.DAD_Codigo " &
                              "LEFT JOIN TipoDocumento td1 " &
                              "ON td1.TDO_Codigo = dpad.TDOC_Proveedor AND td1.TDO_Activo = '1' " &
                              "LEFT JOIN Documento_Cliente_Almacen_Detalle dcad " &
                              "ON dcad.CIA_Almacen = dad.CIA AND dcad.SUC_Almacen = dad.SUC AND dcad.TDOC_Almacen = dad.TDOC_Codigo " &
                              "AND dcad.DCA_Codigo = dad.DCA_Codigo AND dcad.DAD_Codigo = dad.DAD_Codigo " &
                              "LEFT JOIN TipoDocumento td2 " &
                              "ON td2.TDO_Codigo = dcad.TDOC_Cliente AND td2.TDO_Activo = '1' " &
                              "WHERE ku.CIA = '01' AND ku.SUC_Codigo = '01' " &
                              "AND ku.ART_Codigo = @ART AND ku.UBI_Codigo = @UBI " &
                              "AND ku.KUC_Activo = '1' " &
                              "AND CAST(CONVERT(VARCHAR(15),ku.KUC_Fecha,103) AS DATETIME) BETWEEN @INI AND @FIN"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@ART", codArticulo)
            .Parameters.AddWithValue("@UBI", codUbicacion)
            .Parameters.AddWithValue("@INI", fechaInicial)
            .Parameters.AddWithValue("@FIN", fechaFinal)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

End Class